package com.pignest.hotel.dao.hibernate;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.pignest.framework.dao.BaseHibernateDao;
import com.pignest.hotel.beans.CustomHistory;
import com.pignest.hotel.beans.Living;
import com.pignest.hotel.beans.LivingCustom;
import com.pignest.hotel.dao.CustomHistoryDao;

public class CustomHistoryHibernateDao extends BaseHibernateDao implements
		CustomHistoryDao {

	public List<CustomHistory> queryCustomsNoPage(final String name,
			final String certificateno, final String certType, final String roomName,
			final String roomTypeId,final String phone,final String cardNo,final String oType,final Date sLiveDate,
			final Date eLiveDate){
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from CustomHistory as b where 1=1");
				if(name!=null&&!name.equals(""))
					hql.append(" and b.name like :Name");
				if(certificateno!=null&&!certificateno.equals(""))
					hql.append(" and b.certificateno like :Certificateno");
				if(certType!=null&&!certType.equals(""))
					hql.append(" and b.certificateType = :CertType");
				if(roomName!=null&&!roomName.equals(""))
					hql.append(" and b.room.name = :RoomName");
				if(roomTypeId!=null&&!roomTypeId.equals(""))
					hql.append(" and b.room.type.id = :RoomTypeId");
				if(phone!=null&&!phone.equals(""))
					hql.append(" and b.phone like :Phone");
				if(cardNo!=null&&!cardNo.equals(""))
					hql.append(" and b.member.cardNo like :CardNo");
                if(oType!=null&&!oType.equals(""))
                	hql.append(" and b.openType= :OpenType");
				if (sLiveDate != null)
					hql.append(" and b.liveTime>=:SLiveTime");
	            if(eLiveDate!=null)
	            	hql.append(" and b.liveTime<=:ELivingTime");
	            hql.append(" order by b.liveTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				
				if(name!=null&&!name.equals(""))
					query.setString("Name", '%' + name.replaceAll("%", "%%") + '%');
				if(certificateno!=null&&!certificateno.equals(""))
					query.setString("Certificateno",'%'+ certificateno.replaceAll("%", "%%") + '%');
				if(certType!=null&&!certType.equals(""))
					query.setString("CertType", certType);
				if(roomName!=null&&!roomName.equals(""))
					query.setString("RoomName", roomName);
				if(roomTypeId!=null&&!roomTypeId.equals(""))
					query.setString("RoomTypeId", roomTypeId);
				if(phone!=null&&!phone.equals(""))
					query.setString("Phone", '%'+phone.replaceAll("%", "%%") + '%');
				if(cardNo!=null&&!cardNo.equals(""))
					query.setString("CardNo", '%'+cardNo.replaceAll("%", "%%") + '%');
				if(oType!=null&&!oType.equals(""))
					query.setString("OpenType", oType);
				if (sLiveDate != null)
					query.setDate("SLiveTime", sLiveDate);
				if (eLiveDate != null)
					query.setDate("ELivingTime", eLiveDate);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}
	@Override
	public List<CustomHistory> queryCustomsPage(final String name,
			final String certificateno, final String certType, final String roomName,
			final String roomTypeId,final String phone,final String cardNo,final String oType,final Date sLiveDate,
			final Date eLiveDate, final int start,final int length) {
		// TODO Auto-generated method stub
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from CustomHistory as b where 1=1");
				if(name!=null&&!name.equals(""))
					hql.append(" and b.name like :Name");
				if(certificateno!=null&&!certificateno.equals(""))
					hql.append(" and b.certificateno like :Certificateno");
				if(certType!=null&&!certType.equals(""))
					hql.append(" and b.certificateType = :CertType");
				if(roomName!=null&&!roomName.equals(""))
					hql.append(" and b.room.name = :RoomName");
				if(roomTypeId!=null&&!roomTypeId.equals(""))
					hql.append(" and b.room.type.id = :RoomTypeId");
				if(phone!=null&&!phone.equals(""))
					hql.append(" and b.phone like :Phone");
				if(cardNo!=null&&!cardNo.equals(""))
					hql.append(" and b.member.cardNo like :CardNo");
				if(oType!=null&&!oType.equals(""))
                	hql.append(" and b.openType= :OpenType");
				if (sLiveDate != null)
					hql.append(" and b.liveTime>=:SLiveTime");
	            if(eLiveDate!=null)
	            	hql.append(" and b.liveTime<=:ELivingTime");
	            hql.append(" order by b.liveTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				
				if(name!=null&&!name.equals(""))
					query.setString("Name", '%' + name.replaceAll("%", "%%") + '%');
				if(certificateno!=null&&!certificateno.equals(""))
					query.setString("Certificateno",'%'+ certificateno.replaceAll("%", "%%") + '%');
				if(certType!=null&&!certType.equals(""))
					query.setString("CertType", certType);
				if(roomName!=null&&!roomName.equals(""))
					query.setString("RoomName", roomName);
				if(roomTypeId!=null&&!roomTypeId.equals(""))
					query.setString("RoomTypeId", roomTypeId);
				if(phone!=null&&!phone.equals(""))
					query.setString("Phone", '%'+phone.replaceAll("%", "%%") + '%');
				if(cardNo!=null&&!cardNo.equals(""))
					query.setString("CardNo", '%'+cardNo.replaceAll("%", "%%") + '%');
				if(oType!=null&&!oType.equals(""))
					query.setString("OpenType", oType);
				if (sLiveDate != null)
					query.setDate("SLiveTime", sLiveDate);
				if (eLiveDate != null)
					query.setDate("ELivingTime", eLiveDate);
				query.setFirstResult(start);
				query.setMaxResults(length);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}
	@Override
	public CustomHistory queryLastLivingCustom(final String name){
		StringBuilder hql = new StringBuilder();
		hql.append(" from CustomHistory b where 1=1 and b.name = ? ");
		hql.append(" order by opTime desc");
		List<CustomHistory> r = getHibernateTemplate().find(hql.toString(),new Object[]{name});
		if (r == null || r.isEmpty())
			return null;
		return r.get(0);
	}
	public List<LivingCustom> queryCustoms(Living l){
		StringBuilder hql = new StringBuilder();
		hql.append(" from LivingCustom b where 1=1 and b.living = ? ");
		List<LivingCustom> r = getHibernateTemplate().find(hql.toString(),new Object[]{l});
		return r;
	}
	

}
